package com.TT.poi;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.Test;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.Date;

public class ExcelRead {


    @Test
    public void read03() throws IOException {

        //获取文件流
        FileInputStream inputStream = new FileInputStream(getPath() + "03.xls");


        //1.创建一个工作簿，使用excel能操作的这边都可以操作
        Workbook workbook = new HSSFWorkbook(inputStream);

        //得到表
        Sheet sheet = workbook.getSheetAt(0);

        //得到行
        Row row = sheet.getRow(0);

        //得到列
        Cell cell = row.getCell(0);

        System.out.println(cell.getStringCellValue());

        inputStream.close();


    }

    @Test
    public void read07() throws IOException {

        //获取文件流
        FileInputStream inputStream = new FileInputStream(getPath() + "07.xlsx");


        //1.创建一个工作簿，使用excel能操作的这边都可以操作
        Workbook workbook = new XSSFWorkbook(inputStream);

        //得到表
        Sheet sheet = workbook.getSheetAt(0);

        //得到行
        Row row = sheet.getRow(0);

        //得到列
        Cell cell = row.getCell(0);

        System.out.println(cell.getStringCellValue());

        inputStream.close();


    }


    @Test
    public void testCellType() throws IOException {
        //获取文件流
        FileInputStream inputStream = new FileInputStream(getPath() + "03.xls");

        Workbook workbook = new HSSFWorkbook(inputStream);

        Sheet sheet = workbook.getSheetAt(0);

        Row rowTitle = sheet.getRow(0);
        if (rowTitle != null) {
            //获取到当前行的总列数
            int cellCount = rowTitle.getPhysicalNumberOfCells();
            for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                Cell cell = rowTitle.getCell(cellNum);
                if (cell != null) {
                    //获取到类型
                    int cellType = cell.getCellType();

                    String stringCellValue = cell.getStringCellValue();
                    System.out.print(stringCellValue + "|");

                }
            }
            System.out.println();
        }

        //获取表中的内容
        int rowCount = sheet.getPhysicalNumberOfRows();
        for (int rowNum = 0; rowNum < rowCount; rowNum++) {
            Row rowData = sheet.getRow(rowNum);
            if (rowData != null) {
                //读取列
                int cellCount = rowTitle.getPhysicalNumberOfCells();
                for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                    Cell cell = rowData.getCell(cellNum);
                    String cellValue = "";
                    //匹配列的数据类型
                    if(cell!=null){
                        //获取到类型
                        int cellType = cell.getCellType();

                        switch (cellType) {
                            case HSSFCell.CELL_TYPE_STRING:
                                System.out.println("【STRING】");
                                cellValue = cell.getStringCellValue();
                                break;
                            case HSSFCell.CELL_TYPE_BOOLEAN:
                                System.out.println("【BOOLEAN】");
                                cellValue = String.valueOf(cell.getBooleanCellValue());
                                break;
                            case HSSFCell.CELL_TYPE_BLANK:
                                System.out.println("【BLANK】");
                                break;
                            case HSSFCell.CELL_TYPE_NUMERIC:
                                if(HSSFDateUtil.isCellDateFormatted(cell)){
                                    System.out.println("【日期】");
                                    Date date = cell.getDateCellValue();
                                    cellValue = new DateTime(date).toString("yyyy-MM-dd");
                                } else {
                                    System.out.println("【转换为字符串输出】");
                                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                    cellValue = cell.toString();
                                }
                                break;
                            case HSSFCell.CELL_TYPE_ERROR:
                                System.out.println("【数据类型错误】");
                                break;
                        }
                        System.out.println(cellValue);

                    }
                }
            }
            inputStream.close();
        }


    }


    /**
     * 计算公式
     */
    @Test
    public void formula() throws IOException {
        FileInputStream inputStream = new FileInputStream(getPath() + "公式.xls");
        Workbook workbook = new HSSFWorkbook(inputStream);
        Sheet sheet = workbook.getSheetAt(0);

        Row row = sheet.getRow(4);
        Cell cell = row.getCell(0);

        //拿到计算公式 eval
        HSSFFormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);

        //输出单元格的内容
        int cellType = cell.getCellType();
        switch (cellType) {
            case Cell.CELL_TYPE_FORMULA: //公式
                String formula = cell.getCellFormula();
                System.out.println(formula);

                //计算
                CellValue evaluate = formulaEvaluator.evaluate(cell);
                String cellValue = evaluate.formatAsString();
                System.out.println(cellValue);
                break;

        }
    }

    /**
     * 获取项目路径
     *
     * @throws IOException
     */
    private String getPath() throws IOException {
        File directory = new File("");
        return directory.getCanonicalPath() + File.separator;
    }

}
